# #!/usr/bin/env python
# # -*- encoding: utf-8 -*-
# '''
# @文件    :Untitled-3
# @说明    :
# @时间    :2020/05/06 21:09:48
# @作者    :王舰航
# @版本    :3.0
# '''
# # 创建一个留言板的表（ID，留言主题，留言人，留言时间）4个字段，注意，字段请用英文；
# #    完成对这个表记录的增，删，改，查询；
# #    用PyMySQL驱动方式

# import pymysql
 
# # 打开数据库连接
# #db = pymysql.connect("localhost","testuser","w406833650","test" )

# db= pymysql.connect(host="localhost",user="root",
#  	password="w406833650",db="test",port=3306)

# # 使用cursor()方法获取操作游标 
# cursor = db.cursor()
 
# # SQL 插入语句
# sql = """INSERT INTO message_board( ID,
#          Subject, Name, Time )
#          VALUES ('1001', 'Miss you', 'whh', '21:41')"""
# try:
#    # 执行sql语句
#    cursor.execute(sql)
#    # 提交到数据库执行
#    db.commit()
# except:
#    # 如果发生错误则回滚
#    db.rollback()
 
# # 关闭数据库连接
# db.close()



# db= pymysql.connect(host="localhost",user="root",
#  	password="w406833650",db="test",port=3306)

# # 使用cursor()方法获取操作游标
# cur = db.cursor()
 
# #1.查询操作
# # 编写sql 查询语句  user 对应我的表名
# sql = "select * from message_board"
# try:
# 	cur.execute(sql) 	#执行sql语句
 
# 	results = cur.fetchall()	#获取查询的所有记录
# 	print( 'ID', 'Subject', 'Name', 'Time')
# 	#遍历结果
# 	for row in results :
# 		ID = row[0]
# 		Subject = row[1]
# 		Name = row[2]
#       Time = row[3]
# 		print(ID, Subject, Name, Time)
# except Exception as e:
# 	raise e
# finally:
# 	db.close()	#关闭连接


# db= pymysql.connect(host="localhost",user="root",
#  	password="w406833650",db="test",port=3306)
 
# # 使用cursor()方法获取操作游标
# cur = db.cursor()
 
# sql_update ="update message_board set Name = '%s' where ID = %d"
 
# try:
# 	cur.execute(sql_update % ("whh",1003))  #像sql语句传递参数
# 	#提交
# 	db.commit()
# except Exception as e:
# 	#错误回滚
# 	db.rollback() 
# finally:
# 	db.close()


# db= pymysql.connect(host="localhost",user="root",
#  	password="w406833650",db="test",port=3306)
 
# # 使用cursor()方法获取操作游标
# cur = db.cursor()
 
# sql_delete ="delete from message_board where ID = %d"
 
# try:
# 	cur.execute(sql_delete % (1003))  #像sql语句传递参数
# 	#提交
# 	db.commit()
# except Exception as e:
# 	#错误回滚
# 	db.rollback() 
# finally:
# 	db.close()


import pymysql

if __name__ == '__main__':

    db = pymysql.connect( 'localhost', 'root', 'w406833650', 'test' )

    create_table = """CREATE TABLE MESSAGE(
                ID INT AUTO_INCREMENT PRIMARY KEY,
                Title varchar(20),
                Commenter varchar(10),
                Time DATETIME)"""
    cursor = db.cursor()

    cursor.execute( 'DROP TABLE IF EXISTS MESSAGE' )
    cursor.execute( create_table )
    
    add1 = "INSERT INTO MESSAGE( Title, Commenter, Time ) VALUES( 'Learning Python', 'Jason', '2020-05-06 18:00' )"
    add2 = "INSERT INTO MESSAGE( Title, Commenter, Time ) VALUES( 'Back to school', 'Bruce', '2020-05-06 20:00' )"

    cursor.execute( add1 )
    cursor.execute( add2 )
    print( 'Number of the record(s): ', cursor.lastrowid )

    query = "SELECT * FROM MESSAGE"
    cursor.execute( query )
    results = cursor.fetchall()
    for result in results:
        print( result )
    print( '*' * 100 )

    cursor.execute( 'DELETE FROM MESSAGE WHERE ID=1' )
    print( 'After deleting:' )
    cursor.execute( query )
    results = cursor.fetchall()
    for result in results:
        print( result )

    db.close()